﻿unit SchetFrm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, JvExControls, JvXPCore, JvXPBar, ComCtrls, JvExComCtrls, JvComCtrls,
  ExtCtrls, DBGridEh, GridsEh, {GridToXLS,} {SetGridEh,}
  cxGridExportLink,
  dxRibbonForm,
  JvFormPlacement, JvComponentBase, JvAppStorage, JvAppIniStorage, DB,
  StdCtrls, FIBDataSet, pFIBDataSet, JvExMask, JvToolEdit, JvBaseEdits, Mask,
  DBCtrlsEh, JvXPButtons, MemTableDataEh, MemTableEh, JvProgressBar,
  JvXPProgressBar, ImgList, ToolWin, FIBQuery, pFIBCacheQueries, DBLookupEh,
  dxSkinsCore, dxSkinSeven, dxSkinsdxRibbonPainter, cxClasses, dxRibbon,
  cxControls, cxStyles, dxSkinscxPCPainter, cxCustomData, cxGraphics, cxFilter,
  cxData, cxDataStorage, cxEdit, cxDBData, cxGridCustomTableView,
  cxGridTableView, cxGridBandedTableView, cxGridDBBandedTableView,
  cxGridCustomView, cxGridLevel, cxGrid, cxGridCustomPopupMenu, cxGridPopupMenu,
  dxSkinMcSkin, cxLookAndFeels, cxLookAndFeelPainters, dxSkinsdxBarPainter,
  dxBar, dxSkinOffice2010Blue, dxSkinOffice2010Silver, dxSkinOffice2010Black;

type
  TSchetForm = class(Tdxcustomribbonform)
    Panel1: TPanel;
    PageControl: TJvPageControl;
    Page1: TTabSheet;
    JvXPBar1: TJvXPBar;
    ExportExcel: TJvXPBar;
    PlatType: TTabSheet;
    SchetList: TTabSheet;
    KontragentGrid: TDBGridEh;
    PlatTypeGrid: TDBGridEh;
    JvAppIniFileStorage1: TJvAppIniFileStorage;
    JvFormStorage1: TJvFormStorage;
    KontragentSource: TDataSource;
    SrvSource: TDataSource;
    SchetSource: TDataSource;
    page4: TTabSheet;
    CardPanel: TPanel;
    Panel2: TPanel;
    CardPanel1: TPanel;
    Panel3: TPanel;
    CardGrid1: TDBGridEh;
    CardGrid2: TDBGridEh;
    CardGrid3: TDBGridEh;
    CardSource1: TDataSource;
    CardSource2: TDataSource;
    CardSource3: TDataSource;
    DS1: TpFIBDataSet;
    DS1YM: TFIBStringField;
    DS1SALDO1: TFIBBCDField;
    DS1PSUM: TFIBBCDField;
    DS1RSUM: TFIBBCDField;
    DS1SALDO2: TFIBBCDField;
    Panel5: TPanel;
    SchetInfo: TLabel;
    MasterInfo: TLabel;
    AddrInfo: TLabel;
    DS2: TpFIBDataSet;
    DS2VAL_DATE: TFIBDateField;
    DS2PRV_NAME: TFIBStringField;
    DS2PSUM: TFIBBCDField;
    DS2RSUM: TFIBBCDField;
    DS2SALDO: TFIBBCDField;
    DS3: TpFIBDataSet;
    DS3PRVTYPE_NAME: TFIBStringField;
    DS3SRV: TFIBStringField;
    DS3SRV_NAME: TFIBStringField;
    DS3VAL_DATE: TFIBDateField;
    DS3PAY_DATE: TFIBDateField;
    DS3CREATE_DATE: TFIBDateField;
    DS3PSUM: TFIBBCDField;
    DS3RSUM: TFIBBCDField;
    DS3NOTE: TFIBStringField;
    Page5: TTabSheet;
    Panel6: TPanel;
    Label3: TLabel;
    M: TJvCalcEdit;
    JvXPButton1: TJvXPButton;
    DolgGrid: TDBGridEh;
    DolgDS: TpFIBDataSet;
    DolgSource: TDataSource;
    DolgDSACCCODE: TFIBStringField;
    DolgDSMASTER: TFIBStringField;
    DolgDSADDR_NAME: TFIBStringField;
    DolgDSDOLG: TFIBBCDField;
    DolgDSKOEF: TFIBBCDField;
    JvXPBar3: TJvXPBar;
    SFGrid: TDBGridEh;
    SFTable: TMemTableEh;
    SFSource: TDataSource;
    Panel7: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    D1: TDateTimePicker;
    D2: TDateTimePicker;
    StatePanel: TPanel;
    DolgDSM: TFIBSmallIntField;
    ToolBar1: TToolBar;
    CheckAllBtn: TToolButton;
    UnCheckAll: TToolButton;
    SaveSrvBtn: TToolButton;
    OpenSrvBtn: TToolButton;
    ImageList1: TImageList;
    DolgDST: TSmallintField;
    StateImage: TImageList;
    Panel4: TPanel;
    Label4: TLabel;
    UserDS: TpFIBDataSet;
    UserSource: TDataSource;
    JvXPButton2: TJvXPButton;
    User: TDBLookupComboboxEh;
    Kontragent: TDBLookupComboboxEh;
    JvXPButton3: TJvXPButton;
    JvXPButton4: TJvXPButton;
    DolgDSSUM_S: TFIBBCDField;
    DolgDSSUM_P: TFIBBCDField;
    DolgDSSUM_R: TFIBBCDField;
    DolgDSPHONE: TFIBStringField;
    OpenDialog: TOpenDialog;
    ToolButton1: TToolButton;
    RibbonTab1: TdxRibbonTab;
    Ribbon: TdxRibbon;
    l1: TcxGridLevel;
    grd1: TcxGrid;
    v1: TcxGridDBBandedTableView;
    clv1STATE: TcxGridDBBandedColumn;
    clv1CODE: TcxGridDBBandedColumn;
    clv1MASTER: TcxGridDBBandedColumn;
    clv1ADDR: TcxGridDBBandedColumn;
    clv1ADDR_NAME: TcxGridDBBandedColumn;
    clv1KONTRAGENT: TcxGridDBBandedColumn;
    clv1KONTRAGENT_NAME: TcxGridDBBandedColumn;
    clv1DATE_OPEN: TcxGridDBBandedColumn;
    clv1DATE_CLOSE: TcxGridDBBandedColumn;
    clv1TOTAL_SQ: TcxGridDBBandedColumn;
    clv1LIVING_SQ: TcxGridDBBandedColumn;
    clv1LODGER_CNT: TcxGridDBBandedColumn;
    clv1PHONE: TcxGridDBBandedColumn;
    clv1TYPEFLAT: TcxGridDBBandedColumn;
    clv1TYPEFLAT_NAME: TcxGridDBBandedColumn;
    clv1PHONE1: TcxGridDBBandedColumn;
    clv1PHONE2: TcxGridDBBandedColumn;
    clv1PHONE3: TcxGridDBBandedColumn;
    ppm1: TcxGridPopupMenu;
    FDSchet: TpFIBDataSet;
    table_schet: TMemTableEh;
    bar1: TdxBarManager;
    br_sch: TdxBar;
    btn1: TdxBarButton;
    btn_load: TdxBarButton;
    procedure ClickXPButton(Sender: TObject);
    procedure ExportExcelClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure PageControlResize(Sender: TObject);
    procedure SetCard(AccCode: string);
    procedure SchetGridKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure CardSource2DataChange(Sender: TObject; Field: TField);
    procedure JvXPButton1Click(Sender: TObject);
    procedure DolgGridKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure D1Change(Sender: TObject);
    procedure SFGridDblClick(Sender: TObject);
    procedure SFGridKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure CheckAllBtnClick(Sender: TObject);
    procedure UnCheckAllClick(Sender: TObject);
    procedure SaveSrvBtnClick(Sender: TObject);
    procedure OpenSrvBtnClick(Sender: TObject);
    procedure DolgDSTGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure JvXPButton2Click(Sender: TObject);
    procedure JvXPButton3Click(Sender: TObject);
    procedure JvXPButton4Click(Sender: TObject);
    procedure JvXPBar1Items5Click(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure v1DataControllerFilterBeforeChange(
      Sender: TcxDBDataFilterCriteria; ADataSet: TDataSet;
      const AFilterText: string);
    procedure v1KeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure FormActivate(Sender: TObject);
    procedure FormDeactivate(Sender: TObject);
    procedure SchetListShow(Sender: TObject);
    procedure SchetListHide(Sender: TObject);
    procedure btn1Click(Sender: TObject);
  private
    _AccCode: string;
    TQ: TFIBQuery;
    function GetActiveGrid: TDBGridEh;
    function GetAccCode(DS: TDataSet): string;
    function GetSrvList: string;
    procedure SetSrvList(S: string);
    procedure SrvMark(AMark: boolean);
    procedure SrvListSave;
    procedure SrvListLoad;
    //работа с реестром должников
    function ReestrRecState: smallint;
    procedure AddReestr;
    procedure SetInactive(State: smallint);
    procedure TriggedReestrRecord;
    procedure SetMark(Active: boolean);
  public
  end;

var
  SchetForm: TSchetForm;

implementation
 uses cmUtils, SrvFltUnit, NameFrm, SrvSaveListFrm, VolSchetFrm, DModule,
  GetSprForm, scin, MainFrm, ChangeCollectorFrm;


{$R *.dfm}

//==============================================================================
procedure TSchetForm.ToolButton1Click(Sender: TObject);
 var S,K: string;
     Q: TFIBQuery;
begin
 S:=GetSrvList;
 K:=dm1.ds_Kontragent.FN('Name').AsString;
 if Application.MessageBox(PChar('Присвоить текущий список начислений - '+K+' ?'),'',1)<>idOk then Exit;
 Q:=GetQueryForUse(dm1.trnWr, 'UPDATE spr$Kontragent SET Srv='#39+S+#39+' WHERE Code='#39+
                   dm1.ds_Kontragent.FN('Code').AsString+#39);
 try
  dm1.trnWr.StartTransaction;
  Q.ExecQuery;
  dm1.trnWr.Commit;
 finally
  Q.Free;
 end;
 ShowMessage('Список начислений успешно присвоен.');
end;

procedure TSchetForm.ClickXPButton(Sender: TObject);
begin
 PageControl.ActivePageIndex:=TJvXPBarItem(Sender).Tag;

 if PageControl.ActivePageIndex=3 then
    if _AccCode='' then SetCard(GetAccCode(dm1.ds_Schet))
                   else SetCard(_AccCode);
end;

procedure TSchetForm.ExportExcelClick(Sender: TObject);
begin
 //SaveAsExcel(GetActiveGrid, nil, '');
end;

procedure TSchetForm.FormActivate(Sender: TObject);
begin
 SetTabActive('spr',True);
 if PageControl.ActivePage=SchetList then
 if (UserCod='2') or (UserCod='4') then
 MainForm.br_schet.Visible:=true;
end;

procedure TSchetForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
 RemoveBtn(Name);
 Action:=caFree;
 FreeQueryForUse(TQ);
end;

procedure TSchetForm.FormCreate(Sender: TObject);
begin
 AddButton(self);
 PageControl.ActivePageIndex:=2;
 dm1.ds_Kontragent_lookup.CloseOpen(true);
 dm1.ds_Kontragent.CloseOpen(false);
 dm1.ds_Srv_lookup.CloseOpen(true);
 dm1.ds_Srv.CloseOpen(false);
 FDSchet.Open;
 //dm1.ds_Schet.CloseOpen(false);
 UserDS.Open; //просмотр списка пользвателей
 User.SelectNextValue(true);
 Kontragent.SelectNextValue(true);
 SFGrid.DataSource:=nil;
 InitSFTable(SFTable);
 SFGrid.DataSource:=SFSource;
 SFGrid.AutoFitColWidths:=true;
 //запрос для получения текущего статуса счета
 TQ:=GetQueryForUse(dm1.trnRD, 'select * from Vic_GETSCHETSTATE(:Schet)');
 v1.ApplyBestFit();
 if (UserCod='2') or (UserCod='4') then
 MainForm.br_schet.Visible:=true else
 MainForm.br_schet.Visible:=False;;
end;

procedure TSchetForm.FormDeactivate(Sender: TObject);
begin
 //SetTabActive('spr',False);
 MainForm.br_schet.Visible:=False;
end;

function TSchetForm.GetActiveGrid: TDBGridEh;
begin
 case PageControl.ActivePageIndex of
  0: Result:=KontragentGrid;
  1: Result:=PlatTypeGrid;
  //2: Result:=SchetGrid;
  3: Result:=CardGrid2;
  else Result:=DolgGrid;
 end;
end;

procedure TSchetForm.PageControlResize(Sender: TObject);
begin
 //изменение размера формы
 CardPanel.Height:=PageControl.Height div 2;
 CardPanel1.Width:=PageControl.Width div 2;
end;

procedure TSchetForm.SchetGridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 case Key of
  VK_RETURN: if Shift=[ssCtrl] then
   begin
    PageControl.ActivePageIndex:=3;
    SetCard(GetAccCode(dm1.ds_Schet));
   end;
 end;
end;
{------------------------------------------------------------------------------
  SchetListHide:  Отображаем / скрываем панель
_______________________________________________________________________________}
procedure TSchetForm.SchetListHide(Sender: TObject);
begin
  MainForm.br_schet.Visible:=False;
end;

procedure TSchetForm.SchetListShow(Sender: TObject);
begin
  if (UserCod='2') or (UserCod='4') then
  MainForm.br_schet.Visible:=True;
end;
{------------------------------------------------------------------------------}

procedure TSchetForm.D1Change(Sender: TObject);
begin
 PageControl.ActivePageIndex:=3;
 if _AccCode<>'' then SetCard(_acccode)
                 else SetCard(GetAccCode(dm1.ds_Schet));
end;

procedure TSchetForm.DolgDSTGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin //возвращает код текущего статуса записи
 TQ.ParamByName('Schet').AsString:=DolgDS.FN('Code').AsString;
 TQ.ExecQuery;
 Text:=TQ.FN('T').AsString;
 TQ.Close;
end;

procedure TSchetForm.DolgGridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 case Key of
  VK_RETURN: if Shift=[ssCtrl] then
   begin
    PageControl.ActivePageIndex:=3;
    SetCard(GetAccCode(DolgDS));
   end;
  VK_SPACE: TriggedReestrRecord;
 end;
end;

function TSchetForm.GetAccCode(DS: TDataSet): string;
begin
 SchetInfo.Caption:=''; MasterInfo.Caption:=''; AddrInfo.Caption:='';
 Result:='';  _AccCode:='';
 if DS.IsEmpty then Exit;
 Result:=DS.FieldByName('Code').AsString;
 //глобальная переменная
 _AccCode:=Result;
 //информация
 SchetInfo.Caption:=_AccCode;
 MasterInfo.Caption:=DS.FieldByName('Master').AsString;
 AddrInfo.Caption:=DS.FieldByName('Addr_Name').AsString;
end;

procedure TSchetForm.SetCard(Acccode: string);
begin
 DS1.Close; DS2.Close;
 if AccCode='' then Exit;
 //карточка по месяцам
 DS1.ParamByName('AccCode').AsString:=AccCode;
 DS1.ParamByName('SrvList').AsString:=GetSrvList;
 DS1.ParamByName('D1').AsString:=FormatDateTime('YYMM', D1.Date);
 DS1.ParamByName('D2').AsString:=FormatDateTime('YYMM', D2.Date);
 DS1.Open;
 //InitFooterGrid(CardGrid1);
 //карточка по дням
 DS2.ParamByName('AccCode').AsString:=AccCode;
 DS2.ParamByName('SrvList').AsString:=GetSrvList;
 DS2.ParamByName('D1').AsDate:=D1.Date;
 DS2.ParamByName('D2').AsDate:=D2.Date;
 DS2.Open;
// InitFooterGrid(CardGrid2);
end;
//смена коллектора

 // ChangeCollectorForm
procedure TSchetForm.btn1Click(Sender: TObject);
var
  form :TChangeCollectorForm;
  i :Integer;
begin
  if FindChild('ChangeCollectorForm')<>nil then
  begin
     FindChild('ChangeCollectorForm').BringToFront;
     DownButton('ChangeCollectorForm');
  end else
  begin
    try
      form := TChangeCollectorForm.Create( Application );
      Form.FormStyle := fsMDIChild;
      MainForm.barmen1.Merge(form.bar1,true);

    except on EOutOfResources do

    end;
  end;
end;

procedure TSchetForm.CardSource2DataChange(Sender: TObject; Field: TField);
begin //обновляетмся при перемещении по карточке взаиморасч. (дни)
 if _AccCode='' then Exit;
 DS3.Close;
 if DS2.IsEmpty then Exit;
 DS3.ParamByName('AccCode').AsString:=_AccCode;
 DS3.ParamByName('SrvList').AsString:=GetSrvList;
 DS3.ParamByName('D1').AsDate:=DS2.FN('Val_Date').AsDateTime;
 DS3.ParamByName('D2').AsDate:=DS2.FN('Val_Date').AsDateTime;
 DS3.Open;
 //InitFooterGrid(CardGrid3);
end;

//------------пометка видов услуг--------------------------------------------
procedure TSchetForm.SFGridDblClick(Sender: TObject);
begin
 SFTable.Edit;
 SFTable.FieldByName('Flag').AsBoolean:=not SFTable.FieldByName('Flag').AsBoolean;
 SFTable.Post;
end;

procedure TSchetForm.SFGridKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 case Key of
  VK_SPACE: SFGridDblClick(nil);
 end;
end;

//-----------------загрузка телефонов-------------------------------------------
procedure TSchetForm.JvXPBar1Items5Click(Sender: TObject);
 var Q: TFIBQuery;
     FileName: string;
     List: TStrings;
     i: integer;
     Code,Phone: string;
begin
 Q:=GetQueryForUse(dm1.trnWr, 'update spr$Schet set Phone1=:Phone where Code=:Code');
 List:=TStringList.Create;
 try
  if OpenDialog.Execute(Handle) then FileName:=OpenDialog.FileName;
  List.LoadFromFile(FileName);
  for i:=0 to List.Count - 1 do
   begin
    Code:=GetWord(List[i],1,';');
    Phone:=UpperCase(GetWord(List[i],2,';'));
    if (Code<>'') and (Phone<>'') and (Phone<>'НЕТ') and (Phone<>'ОТСУТСВУЕТ') then
     begin
      Q.ParamByName('Code').AsString:=Code;
      Q.ParamByName('Phone').AsString:=Phone;
      dm1.trnWr.StartTransaction;
      try
       Q.ExecQuery;
      except
       dm1.trnWr.Rollback;
      end;
      dm1.trnWr.Commit;
     end;
   end;
 finally
  FreeQueryForUse(Q);
  List.Free;
 end;
 ShowMessage('Загрузка данных завершена.');
end;

//-----------------формирование отчета о просроченной задолженности-------------
procedure TSchetForm.JvXPButton1Click(Sender: TObject);
 var StartTime: TDateTime;
begin
 StartTime:=Time;
 StatePanel.Caption:='Формируется отчет. Ждите...';
 Application.ProcessMessages;
 //---------открываем запрос на выборку данных----------------------------------
 DolgDS.ParamByName('Kontragent').AsString:=dm1.ds_Kontragent_lookup.FN('Code').AsString;
 DolgDS.ParamByName('Step').AsInteger:=M.AsInteger;
 DolgDS.ParamByName('SrvList').AsString:=GetSrvList;
 DolgDS.CloseOpen(true);
 DolgSource.DataSet:=DolgDS;

 StatePanel.Caption:='Отчет сформирован. ( Время формирования: '+TimeToStr(Time-StartTime)+')';
end;

//-----------------список типов услуг события панели управления)----------------
function TSchetForm.GetSrvList: string;
begin
 Result:=';';
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
    begin
     if FieldByName('Flag').AsBoolean then
       Result:=Result+FieldByName('Srv').AsString+';';
     Next;
    end;
   First;
  end;
 SFSource.DataSet:=SFTable;
 if Result=';' then Result:='';
end;

procedure TSchetForm.SetSrvList(S: string);
begin
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
     begin
      Edit;
      FieldByName('Flag').AsBoolean:=Pos(';'+FieldByName('Srv').AsString+';', S)<>0;
      Post;
      Next;
     end;
   First;
  end;
 SFSource.DataSet:=SFTable;
end;

procedure TSchetForm.SrvMark(AMark: boolean);
begin
 SFSource.DataSet:=nil;
 with SFTable do
  begin
   First;
   while not Eof do
    begin
     if FieldByName('Flag').AsBoolean<>AMark then
      begin
       Edit;
       FieldByName('Flag').AsBoolean:=AMark;
       Post;
      end;
     Next;
    end;
   First;
  end;
 SFSource.DataSet:=SFTable;
end;

procedure TSchetForm.SrvListSave;
 var Form: TNameForm;
begin
 Form:=TNameForm.Create(Application);
 Form.SrvList:=GetSrvList;
 Form.ShowModal;
 Form.Free;
end;

procedure TSchetForm.SrvListLoad;
 var Form: TSrvSaveListForm;
     S: string;
begin
 S:='';
 Form:=TSrvSaveListForm.Create(Application);
 if Form.ShowModal=mrOk then
    S:=Form.GetSrvList;
 Form.Free;
 if S<>'' then SetSrvList(S);
end;

procedure TSchetForm.CheckAllBtnClick(Sender: TObject);
begin
 SrvMark(true);
end;

procedure TSchetForm.UnCheckAllClick(Sender: TObject);
begin
 SrvMark(false);
end;

procedure TSchetForm.v1DataControllerFilterBeforeChange(
  Sender: TcxDBDataFilterCriteria; ADataSet: TDataSet;
  const AFilterText: string);
var
  sql,s,cnt :string;
{==============================================================================
 CreateSqlClause-> создаем sql условие по заполненым фильтрам в гриде
===============================================================================}
function CreateClause(v1 :TcxGridDBBandedTableView; oper :string='OR'): string;
var ls :TStringList;
     i :Integer;
     s: string;
clause :string;
AFilterCriteriaItem: TcxFilterCriteriaItem;
begin
  ls:=TStringList.Create;
  for I := 0 to v1.ColumnCount - 1 do
  begin
   if v1.Columns[i].DataBinding.FilterCriteriaItem<>nil then
   if v1.Columns[i].DataBinding.FilterCriteriaItem.DisplayValue<>'' then
   begin
    // s:=v1.Columns[i].DataBinding.FilterCriteriaItem.Criteria.FilterText;
    if v1.Columns[i].DataBinding.ValueType='String' then
     ls.Add(' (Upper(SPR$SCHET.'+v1.Columns[i].DataBinding.FieldName+
     ') LIKE '+#39+AnsiUpperCase(v1.Columns[i].DataBinding.FilterCriteriaItem.DisplayValue)+#39+') '+oper) else
    if v1.Columns[i].DataBinding.ValueType='Currency' then
       ls.Add('SPR$SCHET.'+v1.Columns[i].DataBinding.FieldName+
           '= '+#39+v1.Columns[i].DataBinding.FilterCriteriaItem.DisplayValue+#39+' '+oper) else
    if v1.Columns[i].DataBinding.ValueType='DateTime' then
       ls.Add(' (SUBSTRING(SPR$SCHET.'+v1.Columns[i].DataBinding.FieldName+
       ' FROM 1 FOR 11) = CAST ('+#39+(v1.Columns[i].DataBinding.FilterCriteriaItem.DisplayValue)+#39+
       ' AS DATE)) '+oper) else
    if v1.Columns[i].DataBinding.ValueType='Integer' then
    ls.Add('SPR$SCHET.'+v1.Columns[i].DataBinding.FieldName+
           '= '+v1.Columns[i].DataBinding.FilterCriteriaItem.DisplayValue+' '+oper);
   end;
  end;
  if ls.Count>0 then
      clause:=Copy(ls.Text,0, Length(ls.Text)-(Length(oper)+2));
  //if s<>'' then clause:=s;
  Result:=clause;
  ls.Free;
end;

begin
   sql:=
   'SELECT FIRST 2000 SPR$SCHET.CODE,'+
    ' SPR$SCHET.STATE,'+
    ' SPR$SCHET.KONTRAGENT,'+
    ' SPR$KONTRAGENT.NAME KONTRAGENT_NAME,'+
    ' SPR$SCHET.MASTER,'+
    ' SPR$SCHET.ADDR,'+
    ' SPR$ADDR.NAME ADDR_NAME,'+
    ' SPR$SCHET.DATE_OPEN,'+
    ' IIF(SPR$SCHET.DATE_CLOSE>'+#39+'1.1.2000'+#39+
    ',SPR$SCHET.DATE_CLOSE,NULL) DATE_CLOSE,'+
    ' spr$SCHET.Total_SQ,'+
    ' spr$SCHET.Living_SQ,'+
    ' spr$SCHET.Lodger_CNT,'+
    ' SPR$SCHET.Phone,'+
    ' SPR$SCHET.TYPEFLAT,'+
    ' SPR$TYPEFLAT.NAME TYPEFLAT_NAME,'+
    ' SPR$SCHET.Phone1,'+
    ' SPR$SCHET.Phone2,'+
    ' SPR$SCHET.Phone3'+
' FROM'+
    ' SPR$SCHET LEFT JOIN SPR$ADDR ON (SPR$SCHET.ADDR = SPR$ADDR.CODE)'+
    ' LEFT JOIN SPR$TYPEFLAT ON (SPR$SCHET.TYPEFLAT = SPR$TYPEFLAT.CODE)'+
    ' LEFT JOIN SPR$KONTRAGENT ON (SPR$SCHET.KONTRAGENT = SPR$KONTRAGENT.CODE)'+
 ' WHERE '+
    ' @@where_clause% 1 = 1 @';
 if AFilterText<>'' then
 begin
   s:=CreateClause(v1,'AND');
   if s<>'' then
   begin
      //открываем запрос
      OpenDataSets(s,sql,0,FDSchet);
   end;
 end else
 begin
      s:='1=1';
      OpenDataSets(s,sql,0,FDSchet);
 end;
end;

procedure TSchetForm.v1KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 case Key of
  VK_RETURN: if Shift=[ssCtrl] then
   begin
    PageControl.ActivePageIndex:=3;
    SetCard(GetAccCode(FDSchet));
   end;
 end;
end;

procedure TSchetForm.SaveSrvBtnClick(Sender: TObject);
begin
 SrvListSave;
end;

procedure TSchetForm.OpenSrvBtnClick(Sender: TObject);
begin
 SrvListLoad;
end;

//---------------работа с реестром----------------------------------------
procedure TSchetForm.AddReestr;
 var Q: TFIBQuery;  { TODO : Прямых изменений в реестре быть не должно! }
begin
 Q:=GetQueryForUse(dm1.trnWr, 'INSERT INTO Reestr(AccCode,Usr) VALUES(:AccCode, :Usr)');
 try
  Q.ParamByName('AccCode').AsString:=DolgDS.FN('Code').AsString;
  Q.ParamByName('Usr').AsString:=UserDS.FN('Code').AsString;
  dm1.trnWr.StartTransaction;
  Q.ExecQuery;
  dm1.trnWr.Commit;
 finally
  FreeQueryForUse(Q);
 end;
end;

procedure TSchetForm.SetInactive(State: smallint);
 var Q: TFIBQuery;
begin
 if State=1 then State:=0 else State:=1;
                     { TODO : Прямых изменений в реестре быть не должно! }
 Q:=GetQueryForUse(dm1.trnWr, 'UPDATE Reestr SET State=:State WHERE AccCode=:AccCode');
 try
  Q.ParamByName('AccCode').AsString:=DolgDS.FN('Code').AsString;
  Q.ParamByName('State').AsInteger:=State;
  dm1.trnWr.StartTransaction;
  Q.ExecQuery;
  dm1.trnWr.Commit;
 finally
  FreeQueryForUse(Q);
 end;
end;

function TSchetForm.ReestrRecState: smallint;
begin
 TQ.ParamByName('Schet').AsString:=DolgDS.FN('Code').AsString;
 TQ.ExecQuery;
 Result:=TQ.FN('T').AsInteger;
 TQ.Close;
end;

procedure TSchetForm.TriggedReestrRecord;
 var State: smallint;
begin
 if DolgDS.IsEmpty then Exit;
 State:=ReestrRecState;
 if State>=0 then SetInactive(State) else AddReestr;
 DolgDS.Next;
end;

procedure TSchetForm.SetMark(Active: boolean);
 var S: string;
     State: smallint;
     i,Vol,Num: integer;
     Form: TVolSchetForm;
begin
 if DolgDS.IsEmpty then Exit;

 if Active then S:='Операция: Включить в реестр все выбранные счета.'
           else S:='Операция: Исключить из реестра все выбранные счета.';
 S:=S+' Выполнить операцию ?';  Vol:=0;
 if Active then
  begin
   Form:=TVolSchetForm.Create(Application);
   if Form.ShowModal=mrOk then Vol:=Form.Schet.AsInteger
                          else Vol:=-1;
   Form.Free;
   if Vol<0 then Exit;
  end else if Application.MessageBox(PChar(S),'Запрос',1)<>idOk then Exit;

 DolgSource.DataSet:=nil;
 Application.ProcessMessages;
 DolgDS.First; i:=0; Num:=Vol;
 while not DolgDS.Eof do
  begin
   State:=ReestrRecState;
   if Active then
     begin
      if State=-1 then AddReestr
      else if State=0 then SetInactive(State);
      if State<>1 then
       begin
        Inc(i);
        if Vol>0 then Dec(Num);
        if (Num=0) and (Vol>0) then Break;
       end;
     end
   else if State=1 then
         begin
          SetInactive(State);
          Inc(i);
         end;
   DolgDS.Next;
  end;
 DolgSource.DataSet:=DolgDS;
 //сообщение о завершении хода операции пометки данных
 S:='Операция успешно завершена.'#13;
 if Active then S:=S+'В рееcтр добавлено '+IntToStr(i)+' счетов.'
           else S:=S+'Из реестра удалено '+IntToStr(i)+' счетов.';
 ShowMessage(S);
end;

procedure TSchetForm.JvXPButton2Click(Sender: TObject);
begin
 TriggedReestrRecord;
end;

procedure TSchetForm.JvXPButton3Click(Sender: TObject);
begin
 SetMark(true);
end;

procedure TSchetForm.JvXPButton4Click(Sender: TObject);
begin
 SetMark(false);
end;

end.
